# _*_ coding:utf-8 _*_
# @Time  : 2023.08.24
# @Author: zizlee

# 查询数据

from db_utils import BaseDatabaseConnection


class ExDoctorDatabase:
    # 1、资金对账表
    FUND_SQL = """
        SELECT 
          id,ex_date,fund_num,equity_begin,equity_end,fund_in,fund_out,deposit_fn,deposit_ex,
          profit,profit_close,profit_drift,charge,balance  
        FROM ac_fund WHERE ex_date>=%s AND ex_date<=%s AND fund_num=%s 
        ORDER BY ex_date;
    """
    # 2、交易统计表
    EXCHANGE_SQL = """
        SELECT ex_date,fund_num,variety_name,block_name,deposit_fn,profit_close,amount_ex 
        FROM ac_excp WHERE ex_date>=%s AND ex_date<=%s AND fund_num=%s
        ORDER BY ex_date;
    """
    # 3、平仓明细表
    CLOSE_SQL = """
        SELECT id,ex_date,fund_num,variety_name,direction,profit_close,amount_close,date_open,amount_open
        FROM ac_clex WHERE ex_date>=%s AND ex_date<=%s AND fund_num=%s
        ORDER BY ex_date;
    """

    def __init__(self, fund_num, start, end):
        self.fund_num = fund_num
        self.start_date = start
        self.end_date = end

    def query_fund_data(self):  # 资金对账表
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(self.FUND_SQL, [self.start_date, self.end_date, self.fund_num])
            fund_ret_list = list(conn.cursor.fetchall())
        return fund_ret_list

    def query_close_position_data(self):  # 平仓记录表
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(self.CLOSE_SQL, [self.start_date, self.end_date, self.fund_num])
            close_ret_list = list(conn.cursor.fetchall())
        return close_ret_list

    def query_exchange_statistics_data(self):  # 交易统计表
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(self.EXCHANGE_SQL, [self.start_date, self.end_date, self.fund_num])
            ex_ret_list = list(conn.cursor.fetchall())
        return ex_ret_list

    def query_many_data(self, fund: bool = False, close: bool = False, ex_stat: bool = False):
        data = {
            'fund_list': [],  # 资金对账表
            'close_pos': [],  # 平仓记录表
            'ex_stat': []  # 交易统计表
        }
        with BaseDatabaseConnection() as conn:
            if fund:
                conn.cursor.execute(self.FUND_SQL, [self.start_date, self.end_date, self.fund_num])
                data['fund_list'] = list(conn.cursor.fetchall())
            if close:
                conn.cursor.execute(self.CLOSE_SQL, [self.start_date, self.end_date, self.fund_num])
                data['close_pos'] = list(conn.cursor.fetchall())
            if ex_stat:
                conn.cursor.execute(self.EXCHANGE_SQL, [self.start_date, self.end_date, self.fund_num])
                data['ex_stat'] = list(conn.cursor.fetchall())
        return data
